﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace AddBookInfoFromTxtFile
{
    class AddBooks
    {
        private static void AddBooksFromFile(string input)
        {
            BookstoreDataClassesDataContext context = new BookstoreDataClassesDataContext();
            using (context)
            {
                using (StreamReader reader = new StreamReader(input))
                {
                    reader.ReadLine();
                    while (!reader.EndOfStream)
                    {
                        reader.ReadLine();
                        string bookInfo = reader.ReadLine();
                        string[] book = bookInfo.Split(new char[] { '|' });
                        List<int> AuthorIds = new List<int>();
                        foreach (string authorName in book[1].Split(new char[] { ',', ';' }))
                        {
                            int authorId = AddAuthor(authorName.Trim(), context);
                            AuthorIds.Add(authorId);
                        }
                        string title = book[0];
                        string isbn = book[2].Replace("-", "").Trim();
                        decimal price = decimal.Parse(book[3]);
                        int amount = int.Parse(book[4]);
                        string webSite = (book[5] == String.Empty) ? null : book[5];
                        int bookId = AddBook(title, isbn, price, amount, webSite, context);
                        var booksauthors =
                            from ba in context.Books_Authors
                            where ba.BookId == bookId
                            select ba;
                        foreach (var item in booksauthors)
                        {
                            context.Books_Authors.DeleteOnSubmit(item);
                        }
                        context.SubmitChanges();
                        foreach (int authorId in AuthorIds)
                        {
                            AddBooksAuthors(bookId, authorId, context);
                        }
                    }
                }
            }
        }

        private static int AddAuthor(String name, BookstoreDataClassesDataContext context)
        {
            var authorId =
                from a in context.Authors
                where a.Name == name
                select a.AuthorId;

            if (authorId.Count() == 0)
            {
                Author author = new Author
                {
                    Name = name
                };
                context.Authors.InsertOnSubmit(author);
                context.SubmitChanges();
                return author.AuthorId;
            }
            else
            {
                return authorId.First();
            }
        }

        private static int AddBook(string title, string isbn, decimal price, int amount, string website,
            BookstoreDataClassesDataContext context)
        {
            var book =
                from b in context.Books
                where b.ISBN == isbn
                select b;

            if (book.Count() == 0)
            {
                Book bookToAdd = new Book
                {
                    Title = title,
                    ISBN = isbn,
                    Price = price,
                    Amount = amount,
                    WebSite = website
                };
                context.Books.InsertOnSubmit(bookToAdd);
                context.SubmitChanges();
                return bookToAdd.BookId;
            }
            else
            {
                Book updateBook = book.First();
                updateBook.Title = title;
                updateBook.Price = price;
                updateBook.Amount += amount;
                updateBook.WebSite = website;
                context.SubmitChanges();
                return updateBook.BookId;
            }
        }

        private static void AddBooksAuthors(int bookId, int authorId, BookstoreDataClassesDataContext context)
        {
            var connection =
                from ba in context.Books_Authors
                where ba.AuthorId == authorId && ba.BookId == bookId
                select ba;

            if (connection.Count() == 0)
            {
                Books_Author ba = new Books_Author
                {
                    BookId = bookId,
                    AuthorId = authorId
                };
                context.Books_Authors.InsertOnSubmit(ba);
                context.SubmitChanges();
            }
        }

        static void Main()
        {
            AddBooksFromFile("books.txt");
        }
    }
}
